package com.tu.tropicana.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.tu.tropicana.dao.DataSourceManager;
import com.tu.tropicana.dao.EventDao;
import com.tu.tropicana.model.Event;

public class EventDaoImpl extends ConnectionManager implements EventDao {
	
	private static final Logger LOGGER = Logger.getLogger( EventDaoImpl.class );
	
	private DataSourceManager manager;
	
	public EventDaoImpl() {
		
		manager = C3p0DataSourceManager.getInstance();
	}
	
	public List<Event> getEventList() {
		
		List<Event> events = new ArrayList<Event>();
		String sql = "SELECT em_event_id, em_title, em_description, " +
					"em_location, em_date, em_time, em_user_id FROM event_mst"; 
		
		try {
			
			connection = manager.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			
			while( resultSet.next() ){
				
				Event event = new Event();
				event.setId(resultSet.getString("em_event_id"));
				event.setTitle(resultSet.getString("em_title"));
				event.setDescription(resultSet.getString("em_description"));
				event.setLocation(resultSet.getString("em_location"));
				event.setDate(resultSet.getString("em_date"));
				event.setTime(resultSet.getString("em_time"));
				event.setUser(resultSet.getString("em_user_id"));
				
				events.add(event);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			
			closeConnection( connection, statement, resultSet );
			
		}
		
		return events;
	}

	public List<Event> getEventListByDate(String date) {
		
		List<Event> events = new ArrayList<Event>();
		String sql = "SELECT em_event_id, em_title, em_description, " +
					"em_location, em_date, em_time, em_user_id FROM event_mst WHERE em_date=?"; 
		
		try {
			
			connection = manager.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, date.toString());
			resultSet = preparedStatement.executeQuery();
			
			while( resultSet.next() ){
				
				Event event = new Event();
				event.setId(resultSet.getString("em_event_id"));
				event.setTitle(resultSet.getString("em_title"));
				event.setDescription(resultSet.getString("em_description"));
				event.setLocation(resultSet.getString("em_location"));
				event.setDate(resultSet.getString("em_date"));
				event.setTime(resultSet.getString("em_time"));
				event.setUser(resultSet.getString("em_user_id"));
				
				events.add(event);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			
			closeConnection( connection, preparedStatement, resultSet );
			
		}
		return events;
	}

	public int addEvent(Event event) {
		
		int eventId = 0;
		
		String sql = "INSERT INTO event_mst(em_title, em_description, " +
				"em_location, em_date, em_time, em_user_id" +
				" ) VALUES(?,?,?,?,?,?)";

		try {
			
			connection = manager.getConnection();
			
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1,event.getTitle());
			preparedStatement.setString(2,event.getDescription());
			preparedStatement.setString(3,event.getLocation());
			preparedStatement.setString(4,event.getDate());
			preparedStatement.setString(5,event.getTime());
			preparedStatement.setString(6,event.getUser());
			
			preparedStatement.executeUpdate();
			resultSet = preparedStatement.getGeneratedKeys();
			
			while( resultSet.next() ){
				eventId = resultSet.getInt(1);				
			}

		} catch (SQLException e) {
			
			e.printStackTrace();
		} finally {
			
			closeConnection( connection, preparedStatement, resultSet );
			
		}
		return eventId;		
	}

	public Event updateEvent(Event event) {
		
		Event updatedEvent = null;
		
		String sql = "UPDATE event_mst SET em_title=?,em_description=?," +
				"em_location=?,em_date=?,em_time=?,em_user_id=? " +
				"WHERE em_event_id=?";

		try {
			
			connection = manager.getConnection();
			
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1,event.getTitle());
			preparedStatement.setString(2,event.getDescription());
			preparedStatement.setString(3,event.getLocation());
			preparedStatement.setString(4,event.getDate());
			preparedStatement.setString(5,event.getTime());
			preparedStatement.setString(6,event.getUser());
			preparedStatement.setString(7,event.getId());
			
			int rowsAffected = preparedStatement.executeUpdate();
			
			if ( rowsAffected > 0 ) {
				updatedEvent = event;
			}
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		} finally {
			
			closeConnection( connection, preparedStatement, resultSet );
			
		}
		return event;		
	}
	
	public boolean deleteEvent(String id) {
		
		String sql = "DELETE FROM event_mst WHERE em_event_id=?";
		
		try {
			
			connection = manager.getConnection();
			
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1,id);
			
			int rowsAffected = preparedStatement.executeUpdate();
			
			if ( rowsAffected > 0 ){
				return true;
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		} finally {
			
			closeConnection( connection, preparedStatement, resultSet );
			
		}
		
		return false;
		
	}
}
